﻿Imports DTO
Imports System.Data.OleDb

Public Class LoaiPhongDAO

    Public Function LayDanhSach() As List(Of LoaiPhongDto)
        Dim ds As New List(Of LoaiPhongDto)
        Dim cn As OleDbConnection
        Dim strSQL As String
        'B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
        cn = DataProvider.ConnectDB()
        'B3: Tao chuoi strSQL thao tac CSDL
        strSQL = "Select * From LOAIPHONG"
        'B4: Thuc thi chuoi strSQL
        cn.Open()
        Dim cmd As New OleDbCommand(strSQL, cn)

        'Dim reader As OleDbDataReader = cmd.ExecuteReader()
        'Dim danhsach As New List(Of LoaiPhongDto)()
        'While reader.Read()
        '    Dim cnDTO As New LoaiPhongDto()
        '    cnDTO.MaLoaiPhong = reader.GetInt32(0)
        '    cnDTO.TenLoaiPhong = reader.GetString(1)
        '    cnDTO.DonGia = reader.GetDouble(2)
        '    danhsach.Add(cnDTO)

        Dim dr As OleDbDataReader
        dr = cmd.ExecuteReader()
        Dim lp As New LoaiPhongDto()
        While (dr.Read())
            lp = New LoaiPhongDto()
            lp.MaLoaiPhong = dr("MaLoaiPhong")
            lp.TenLoaiPhong = dr("TenLoaiPhong")
            lp.DonGia = dr("DonGia")
            ds.Add(lp)

        End While
        'B5: Dong ket noi CSDL
        cn.Close()
        Return ds
    End Function

    Public Function ThemLoaiPhong(ByVal lp As LoaiPhongDto)

        Dim ketqua As Boolean = True
        Dim conn As OleDbConnection = DataProvider.ConnectDB()
        Try
            conn.Open()
            Dim lenh As OleDbCommand
            Dim sql As String = "INSERT INTO LOAIPHONG (TenLoaiPhong, DonGia ) VALUES('" & lp.TenLoaiPhong & "'," & lp.DonGia & ")"
            lenh = New OleDbCommand(sql, conn)
            Dim i As Integer = lenh.ExecuteNonQuery()
            If i <> 1 Then
                ketqua = False
            End If
        Catch ex As Exception
            ketqua = False
        Finally
            conn.Close()
        End Try
        Return ketqua
      

    End Function

    Public Function CapNhatLoaiPhong(ByVal lp As LoaiPhongDto)

        Dim ketqua As Boolean = True
        Dim conn As OleDbConnection = DataProvider.ConnectDB()
        conn.Open()

        Dim lenh As OleDbCommand
        Dim sql As String = "Update LOAIPHONG Set TenLoaiPhong = @TenPhong,DonGia=@DonGia Where MaLoaiPhong = @MaLoaiPhong"
        lenh = New OleDbCommand(sql, conn)

        lenh.Parameters.Add("@TenPhong", OleDbType.WChar)
        lenh.Parameters.Add("@DonGia", OleDbType.Double)
        lenh.Parameters.Add("@MaLoaiPhong", OleDbType.Integer)

        lenh.Parameters("@MaLoaiPhong").Value = lp.MaLoaiPhong
        lenh.Parameters("@TenPhong").Value = lp.TenLoaiPhong
        lenh.Parameters("@DonGia").Value = lp.DonGia

        lenh.ExecuteNonQuery()
        conn.Close()

        Return ketqua
       
    End Function

End Class
